SpdrParentReviewRequest

V8 Message Definiton

SpdrParentReviewRequest records created when a parent order is submitted with StageType=StageReview. These records indicate that an external review of the parent order has been requested.

METADATA

Attribute	Value
Topic	4195-parent-review
MLink Token	SystemData
Product	SRTrade
accessType	SELECT

Table Definition

Field	Type	Key	Default Value	Comment
parentNumber	CHAR(19)	PRI	'0000-0000-0000-0000'	SPDR order number
sysEnvironment	enum - SysEnvironment		'None'	original source sys environment Stable Current etc
runStatus	enum - RunStatus		'None'	original source run status ProdBeta
parentShape	enum - SpdrOrderShape		'None'	Parent Shape Single Cross MLeg MLegCross
altOrderId	VARCHAR(24)		''	alternate order ID usually clOrdId from client
altPrevOrderId	VARCHAR(24)		''	alternate prev order ID usually origClOrdId from client during cxlreplace
altAccnt	VARCHAR(32)		''	alternate client assigned long account string optional used to map between client and SR account strings
altUserName	VARCHAR(24)		''	alternate client assigned user name optional used to map between client and SR account strings
secKey_at	enum - AssetType		'None'	Composite Security Key
secKey_ts	enum - TickerSrc		'None'	Composite Security Key
secKey_tk	VARCHAR(12)		''	Composite Security Key
secKey_yr	SMALLINT UNSIGNED		0	Composite Security Key
secKey_mn	TINYINT UNSIGNED		0	Composite Security Key
secKey_dy	TINYINT UNSIGNED		0	Composite Security Key
secKey_xx	DOUBLE		0	Composite Security Key
secKey_cp	enum - CallPut		'Call'	Composite Security Key
secType	enum - SpdrKeyType		'None'	Security Type Stock Future Option
securityDesc	TINYTEXT		''	additional security description
accnt	VARCHAR(16)		''	SR trading account
clientFirm	VARCHAR(16)		''	SR client firm
spdrSource	enum - SpdrSource		'None'	from original parent order
groupingCode	CHAR(19)		'0000-0000-0000-0000'	from original parent order
strategy	VARCHAR(36)		''	clientsupplied strategy stringvisible on SpiderRock GUI tools and other order reports
userName	VARCHAR(24)		''	name of the user entering the order
orderSide	enum - BuySell		'None'
orderSize	INT		0	maximum fill size contracts
firmType	enum - FirmType		'None'	used to override firmType in account config
orderCapacity	enum - OrderCapacity		'None'
positionType	enum - PositionType		'None'
ssaleFlag	enum - ShortSaleFlag		'None'	used to determine stock autohedge flags
locateQuan	INT		0	available locate quantity if selling short
locateFirm	VARCHAR(6)		''	firm granting the locate also locate firm used on street FIX orders
locatePool	VARCHAR(16)		''	locate pool firm granting the locate
noCrossGroup	VARCHAR(16)		''
exchTraderId	VARCHAR(16)		''
largeTraderId	VARCHAR(16)		''
tradingLocation	VARCHAR(16)		''
hedgeSecKey_at	enum - AssetType		'None'
hedgeSecKey_ts	enum - TickerSrc		'None'
hedgeSecKey_tk	VARCHAR(12)		''
hedgeSecKey_yr	SMALLINT UNSIGNED		0
hedgeSecKey_mn	TINYINT UNSIGNED		0
hedgeSecKey_dy	TINYINT UNSIGNED		0
hedgeSecType	enum - SpdrKeyType		'None'
hedgeUnits	FLOAT		0
userData1	TINYTEXT		''	client supplied data field passes through to parent and child executions and reports as well as FIX drops
userData2	TINYTEXT		''	client supplied data field passes through to parent and child executions and reports as well as FIX drops
childData	TINYTEXT		''	client supplied data field passes through to down stream child orders
timestamp	DATETIME(6)		'1900-01-01 00:00:00.000000'
OrderLegsList	JSON		'JSON_OBJECT()'

PRIMARY KEY DEFINITION (Unique)

Field	Sequence
parentNumber	1

JSON Block (OrderLegsList)

Field	Type	Comment
ticker	TickerKey	stock ticker
stockSide	enum - stockSide	Buy Sell None
stockShares	int	number of shares included zero if none
stockLegId	long	SR stock leg ID
altStkLegId	string	client stock leg ID usually from a FIX order
ssaleFlag	enum - ssaleFlag	stock short sale flag
numLegs	byte	number of valid legs below
secKey1	OptionKey	leg 1
secType1	enum - secType1
mult1	ushort
side1	enum - side1
legId1	long	SR leg Id
altLegId1	string	client leg Id usually from a FIX order
posType1	enum - posType1
ssaleFlag1	enum - ssaleFlag1
secKey2	OptionKey	leg 2
secType2	enum - secType2
mult2	ushort
side2	enum - side2
legId2	long	SR leg Id
altLegId2	string	client leg Id usually from a FIX order
posType2	enum - posType2
ssaleFlag2	enum - ssaleFlag2
secKey3	OptionKey	leg 3
secType3	enum - secType3
mult3	ushort
side3	enum - side3
legId3	long	SR leg Id
altLegId3	string	client leg Id usually from a FIX order
posType3	enum - posType3
ssaleFlag3	enum - ssaleFlag3
secKey4	OptionKey	leg 4
secType4	enum - secType4
mult4	ushort
side4	enum - side4
legId4	long	SR leg Id
altLegId4	string	client leg Id usually from a FIX order
posType4	enum - posType4
ssaleFlag4	enum - ssaleFlag4
secKey5	OptionKey	leg 5
secType5	enum - secType5
mult5	ushort
side5	enum - side5
legId5	long	SR leg Id
altLegId5	string	client leg Id usually from a FIX order
posType5	enum - posType5
ssaleFlag5	enum - ssaleFlag5
secKey6	OptionKey	leg 6
secType6	enum - secType6
mult6	ushort
side6	enum - side6
legId6	long	SR leg Id
altLegId6	string	client leg Id usually from a FIX order
posType6	enum - posType6
ssaleFlag6	enum - ssaleFlag6

CREATE TABLE EXAMPLE QUERY

CREATE TABLE `SRTrade`.`MsgSRParentReviewRequest` (
    `parentNumber` CHAR(19) NOT NULL DEFAULT '0000-0000-0000-0000' COMMENT 'SPDR order number',
    `sysEnvironment` ENUM('None','Neptune','Pluto','V7_Stable','V7_Latest','Saturn','Venus','Mars','SysTest','V7_Current') NOT NULL DEFAULT 'None' COMMENT 'original (source) sys environment [Stable, Current, etc]',
    `runStatus` ENUM('None','Prod','Beta','UAT','SysTest') NOT NULL DEFAULT 'None' COMMENT 'original (source) run status [Prod,Beta]',
    `parentShape` ENUM('None','Single','Cross','MLeg','MLegCross') NOT NULL DEFAULT 'None' COMMENT 'Parent Shape [Single, Cross, MLeg, MLegCross]',
    `altOrderId` VARCHAR(24) NOT NULL DEFAULT '' COMMENT 'alternate order ID (usually clOrdId from client)',
    `altPrevOrderId` VARCHAR(24) NOT NULL DEFAULT '' COMMENT 'alternate prev order ID (usually origClOrdId from client during cxl/replace)',
    `altAccnt` VARCHAR(32) NOT NULL DEFAULT '' COMMENT 'alternate (client assigned) "long" account string (optional) [used to map between client and SR account strings]',
    `altUserName` VARCHAR(24) NOT NULL DEFAULT '' COMMENT 'alternate (client assigned) user name (optional) [used to map between client and SR account strings]',
    `secKey_at` ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') NOT NULL DEFAULT 'None' COMMENT 'Composite Security Key',
    `secKey_ts` ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','ESX','ANY','CXE','DXE','NXAM','NXBR','NXDUB','NXLS','NXLDN','NXML','NXMLT','NXOS','NXP','EUREX','CEDX','ICEFE') NOT NULL DEFAULT 'None' COMMENT 'Composite Security Key',
    `secKey_tk` VARCHAR(12) NOT NULL DEFAULT '' COMMENT 'Composite Security Key',
    `secKey_yr` SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Composite Security Key',
    `secKey_mn` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Composite Security Key',
    `secKey_dy` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Composite Security Key',
    `secKey_xx` DOUBLE NOT NULL DEFAULT 0 COMMENT 'Composite Security Key',
    `secKey_cp` ENUM('Call','Put','Pair') NOT NULL DEFAULT 'Call' COMMENT 'Composite Security Key',
    `secType` ENUM('None','Stock','Future','Option','MLeg') NOT NULL DEFAULT 'None' COMMENT 'Security Type [Stock, Future, Option]',
    `securityDesc` TINYTEXT NOT NULL DEFAULT '' COMMENT 'additional security description',
    `accnt` VARCHAR(16) NOT NULL DEFAULT '' COMMENT 'SR trading account',
    `clientFirm` VARCHAR(16) NOT NULL DEFAULT '' COMMENT 'SR client firm',
    `spdrSource` ENUM('None','SpdrTicket','SpdrSingle','SRSE','FIX','HedgeTool','TradeHedge','OpenHedge','AutoHedge','Orphan','RiskManager','OrderManager','ManagedOrder','RFQRespSrvr','Legger','SRSEDrop','FixDrop','TicketDrop','SysTest','RFRResponse','AllocOmni','AllocClient','CertGateway','MLegResponse','LeggerX','DropManager','AutoHedgeSrvr','AuctionStrategySrvr','AllocBlockFace','AllocBlockCust','IceChatGateway','EXS2SRC','MLinkResponse','AutoResponderVD','AutoResponderRC','AutoResponderSN','AutoResponderBX','MLink') NOT NULL DEFAULT 'None' COMMENT 'from original parent order',
    `groupingCode` CHAR(19) NOT NULL DEFAULT '0000-0000-0000-0000' COMMENT 'from original parent order',
    `strategy` VARCHAR(36) NOT NULL DEFAULT '' COMMENT 'client-supplied strategy string;visible on SpiderRock GUI tools and other order reports.',
    `userName` VARCHAR(24) NOT NULL DEFAULT '' COMMENT 'name of the user entering the order',
    `orderSide` ENUM('None','Buy','Sell') NOT NULL DEFAULT 'None',
    `orderSize` INT NOT NULL DEFAULT 0 COMMENT 'maximum fill size (contracts)',
    `firmType` ENUM('None','Customer','Firm','MarketMaker','ProCustomer','BrokerDealer','AwayMM','FirmJBO','BrkrDlrCust') NOT NULL DEFAULT 'None' COMMENT 'used to override firmType in account config',
    `orderCapacity` ENUM('None','Agency','Principal','Individual','Proprietary','AgentOtherMember','RisklessPrincipal') NOT NULL DEFAULT 'None',
    `positionType` ENUM('None','Opening','Closing','Auto') NOT NULL DEFAULT 'None',
    `ssaleFlag` ENUM('None','Long','Short','Exempt','Auto','Open','Close','Cover','NA') NOT NULL DEFAULT 'None' COMMENT 'used to determine stock auto-hedge flags',
    `locateQuan` INT NOT NULL DEFAULT 0 COMMENT 'available locate quantity (if selling short)',
    `locateFirm` VARCHAR(6) NOT NULL DEFAULT '' COMMENT 'firm granting the locate (also locate firm used on street FIX orders)',
    `locatePool` VARCHAR(16) NOT NULL DEFAULT '' COMMENT 'locate pool @ firm granting the locate',
    `noCrossGroup` VARCHAR(16) NOT NULL DEFAULT '',
    `exchTraderId` VARCHAR(16) NOT NULL DEFAULT '',
    `largeTraderId` VARCHAR(16) NOT NULL DEFAULT '',
    `tradingLocation` VARCHAR(16) NOT NULL DEFAULT '',
    `hedgeSecKey_at` ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') NOT NULL DEFAULT 'None',
    `hedgeSecKey_ts` ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','ESX','ANY','CXE','DXE','NXAM','NXBR','NXDUB','NXLS','NXLDN','NXML','NXMLT','NXOS','NXP','EUREX','CEDX','ICEFE') NOT NULL DEFAULT 'None',
    `hedgeSecKey_tk` VARCHAR(12) NOT NULL DEFAULT '',
    `hedgeSecKey_yr` SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    `hedgeSecKey_mn` TINYINT UNSIGNED NOT NULL DEFAULT 0,
    `hedgeSecKey_dy` TINYINT UNSIGNED NOT NULL DEFAULT 0,
    `hedgeSecType` ENUM('None','Stock','Future','Option','MLeg') NOT NULL DEFAULT 'None',
    `hedgeUnits` FLOAT NOT NULL DEFAULT 0,
    `userData1` TINYTEXT NOT NULL DEFAULT '' COMMENT 'client supplied data field; passes through to parent and child executions and reports as well as FIX drops',
    `userData2` TINYTEXT NOT NULL DEFAULT '' COMMENT 'client supplied data field; passes through to parent and child executions and reports as well as FIX drops',
    `childData` TINYTEXT NOT NULL DEFAULT '' COMMENT 'client supplied data field; passes through to down stream child orders',
    `timestamp` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000',
    `OrderLegsList` JSON NOT NULL DEFAULT JSON_OBJECT() CHECK(JSON_VALID(OrderLegsList)),
    CONSTRAINT nonnegative_parentNumber CHECK(ASCII(parentNumber) < 56),
    CONSTRAINT nonnegative_groupingCode CHECK(ASCII(groupingCode) < 56),
    PRIMARY KEY USING HASH (`parentNumber`)
) ENGINE=SRSE DEFAULT CHARSET=LATIN1 COMMENT='SpdrParentReviewRequest records created when a parent order is submitted with StageType=StageReview.  These records indicate that an external review of the parent order has been requested.';

SELECT TABLE EXAMPLE QUERY

SELECT
    `parentNumber`,
    `sysEnvironment`,
    `runStatus`,
    `parentShape`,
    `altOrderId`,
    `altPrevOrderId`,
    `altAccnt`,
    `altUserName`,
    `secKey_at`,
    `secKey_ts`,
    `secKey_tk`,
    `secKey_yr`,
    `secKey_mn`,
    `secKey_dy`,
    `secKey_xx`,
    `secKey_cp`,
    `secType`,
    `securityDesc`,
    `accnt`,
    `clientFirm`,
    `spdrSource`,
    `groupingCode`,
    `strategy`,
    `userName`,
    `orderSide`,
    `orderSize`,
    `firmType`,
    `orderCapacity`,
    `positionType`,
    `ssaleFlag`,
    `locateQuan`,
    `locateFirm`,
    `locatePool`,
    `noCrossGroup`,
    `exchTraderId`,
    `largeTraderId`,
    `tradingLocation`,
    `hedgeSecKey_at`,
    `hedgeSecKey_ts`,
    `hedgeSecKey_tk`,
    `hedgeSecKey_yr`,
    `hedgeSecKey_mn`,
    `hedgeSecKey_dy`,
    `hedgeSecType`,
    `hedgeUnits`,
    `userData1`,
    `userData2`,
    `childData`,
    `timestamp`,
    `OrderLegsList`
FROM `SRTrade`.`MsgSRParentReviewRequest`
WHERE 
    /* Replace with a CHAR(19) */
    `parentNumber` = 'Example_parentNumber';

Doc Columns Query

SELECT * FROM SRTrade.doccolumns WHERE TABLE_NAME='SpdrParentReviewRequest' ORDER BY ordinal_position ASC;